package com.ddstation.crm.batchoutput.service;

import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.regex.Pattern;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

import com.ddstation.crm.batchoutput.enmu.ExcelCellDataType;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

@Component @Lazy
public class ExcelUtils {

    //工具类中需要注入service，dao等需要
    //使用注解@PostConstruct把需要使用的service，dao等加载到上面定义的静态内部对象中
    @PostConstruct
    public void init() {
    }

    private ExcelCellDataType[] cellDataType;
    
    /**
     * 指定各列的数据类型
     * */
    public void setCellDataType(ExcelCellDataType[] cellDataType) {
		this.cellDataType = cellDataType;
	}

	/**
     * 
     * excel文件输出，供客户端下载
     * 
     * @param templateFilePath     模板文件路径 
     * @param fileNamePri          生成的下载用的文件名前缀
     * @param List<String[]>       需输出的数据集合，在业务class侧存入数组
     *                             ※模板文件的列数，集合数据的列数一致。不然输出时，表头与数据的对应关系会乱！！！
     * @param response                           
     */
   	public void exportExcel(String fileName,String[] titles,
   		 List<String[]> list, HttpServletResponse response) {
		// TODO Auto-generated method stub
		if (logger.isDebugEnabled()) {
			logger.debug("download 开始=============================================");
		}
		
		WritableWorkbook oDailyWorkBK = null;
		
        try {
            // 写数据
        	response.reset();
        	response.setContentType("text/html;charset=UTF-8");
          	OutputStream fos = new BufferedOutputStream(response.getOutputStream());
        	response.setContentType("application/vnd.ms-excel");
        	String newName = URLEncoder.encode(fileName/* +  System.currentTimeMillis()*/ + ".xls","UTF-8");
			//告诉客户端该文件不是直接解析 而是以附件形式打开(下载)----filename="+filename 客户端默认对名字进行解码
			response.addHeader("Content-Disposition", "attachment;filename=\"" + newName + "\"");
			response.setHeader("Set-Cookie", "fileDownload=true; path=/"); // jQuery.fileDownload.js 文件下 成功响应
			// 获取模板的第一行例子数据
            // 获取所有用户
    		oDailyWorkBK = Workbook.createWorkbook(fos);
    		
    		WritableFont font = new WritableFont(WritableFont.ARIAL,11);
			NumberFormat numberCell = new NumberFormat("##0");
			NumberFormat moneyCell = new NumberFormat("##0.00");
			WritableCellFormat formatTitle = new  WritableCellFormat(font);
			WritableCellFormat formatMessage = new  WritableCellFormat(font);
			WritableCellFormat formatMessagew = new  WritableCellFormat(font);
			WritableCellFormat formatMessageNumber = new  WritableCellFormat(font, numberCell);
			WritableCellFormat formatMessageMoneyNumber = new  WritableCellFormat(font, moneyCell);
//			WritableCellFormat formatfoot = new  WritableCellFormat(font);

			formatTitle.setBackground(Colour.PALE_BLUE);//淡蓝色背景
			formatMessage.setBackground(Colour.GREY_25_PERCENT);//灰色背影
//			formatMessageNumber.setBackground(Colour.GREY_25_PERCENT);//灰色背影
//			formatMessagew.setBackground(Colour.WHITE);//白色背影
//			formatMessagewNumber.setBackground(Colour.WHITE);//白色背影
//			formatfoot.setBackground(Colour.YELLOW);//黄色背影
			
			// sheet生成
    		WritableSheet oInstitutionSheet = oDailyWorkBK.createSheet(fileName, 0);
			Label label;
			WritableCellFormat format1 = formatMessagew;
			format1.setBorder(Border.ALL, BorderLineStyle.THIN);
			formatTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
			formatMessageNumber.setBorder(Border.ALL, BorderLineStyle.THIN);
			formatMessageMoneyNumber.setBorder(Border.ALL, BorderLineStyle.THIN);
			
			// title生成(titles为空的时候)
			if (titles != null ){
				int index = 0;
				for ( String title : titles ){
					// ID(编号)
					label = new Label(index++, 0, title, formatTitle);
					oInstitutionSheet.addCell(label);
				}	
			}
			
			// 数据列
			int maxColLength = 0;
			if ( list.size() > 0 ) {
				maxColLength = list.get(0).length;
			};
			
			jxl.write.Number labelNF = null;
			
			// data部分生成（提取1条记录）
			int i = 1;
			for (String[] rec:list){
				
				int n = 0;
				// 循环所有列
				for (String outValue : rec ){
					
					if ( StringUtils.isEmpty(outValue) ){
						label = new Label(n, i, outValue, format1);
						oInstitutionSheet.addCell(label);
					} else {
						ExcelCellDataType dataType = getCellDataType(outValue, n, maxColLength);
						switch (dataType) {
							case NUMBER:
								labelNF = new jxl.write.Number(n, i, Double.valueOf(outValue), formatMessageNumber);
								oInstitutionSheet.addCell(labelNF);
								break;
							case CURRENCY:	
								labelNF = new jxl.write.Number(n, i, Double.valueOf(outValue), formatMessageMoneyNumber);
								oInstitutionSheet.addCell(labelNF);
								break;
							default:
								label = new Label(n, i, outValue, format1);
								oInstitutionSheet.addCell(label);
								break;
						}
					}
					n++;
				}
				i++;
			}
			
			oDailyWorkBK.write();
			oDailyWorkBK.close();
			
			fos.close();

    		if (logger.isDebugEnabled()) {
    			logger.debug("execl生成 结束=============================================");
    		}

        } catch (Exception e) {
        	throw new RuntimeException("execl生成出错："+e.getMessage());
        } finally {
        	
        }
        
		if (logger.isDebugEnabled()) {
			logger.debug("download 正常结束=============================================");
		}
	}
	
   	/**
   	 * 返回该单元格的数据类型
   	 * */
   	private ExcelCellDataType getCellDataType( String outValue,int colIndex ,int maxColLength){
   		
   		// 有指定数据类型时，返回调用方指定的数据类型
   		if ( this.cellDataType != null && this.cellDataType.length >= maxColLength ){
   			if (this.cellDataType[colIndex] != null) {
   				return this.cellDataType[colIndex];
   			} else {
   				return ExcelCellDataType.TEXT;
   			}
   			
   			// 未指定cell的数据类型时，根据值自由判断
   		} else {
   		// 金额判断
			if (outValue !=null && isMoney (outValue)){
				return ExcelCellDataType.CURRENCY;
			} 
			// 数字判断
			else if (outValue !=null && isNum (outValue)){
				return ExcelCellDataType.NUMBER;
			} else {
				return ExcelCellDataType.TEXT;
			}
   		}
   	}
   	
	public static boolean isNum(String str){

        Pattern pattern = Pattern.compile("^-?[0-9]+");
        if(pattern.matcher(str).matches()){
            //数字
            return true;
        } else {
            //非数字
            return false;
        }
    }
    
	//金额验证
    public static boolean isMoney(String str) 
    { 
    	// 没有发现小数点，直接返回false
    	if (!str.contains(".")) {
            return false;
        }
    	
        Pattern pattern=Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$"); // 判断小数点后2位的数字的正则表达式
        java.util.regex.Matcher match=pattern.matcher(str); 
        if(match.matches()==false) 
        { 
           return false; 
        } 
        else 
        { 
           return true; 
        } 
    }
	
	private Logger logger = LoggerFactory.getLogger(this.getClass());
}